
--table NguoiDung
if OBJECT_ID('NguoiDung') is not null
drop table NguoiDung
go
create table NguoiDung
(
	MaNguoiDung bigint identity(1,1) not null,
	TenDangNhap nvarchar(255) not null,
	MatKhau nvarchar(255) not null,
	Email nvarchar(255) not null,
	Ho nvarchar(255) not null,
	Ten nvarchar(255) not null,
	GioiTinh int not null,
	NgaySinh date not null,
	EmailXacNhan nvarchar(255) not null,
	constraint pk_NguoiDung primary key (MaNguoiDung),
)


go

--table Truong
if OBJECT_ID('Truong') is not null
drop table Truong
go
create table Truong
(
	MaTruong bigint identity(1, 1) not null,
	TenTruong nvarchar(255) not null,
	constraint pk_Truong primary key (MaTruong)
)

go

--table ChuyenNganh
if OBJECT_ID('ChuyenNganh') is not null
drop table ChuyenNganh
go
create table ChuyenNganh
(
	MaChuyenNganh bigint identity(1, 1) not null,
	TenChuyenNganh nvarchar(255) not null,
	constraint pk_ChuyenNganh primary key (MaChuyenNganh)
)

go

--table VaiTroNguoiDung
if OBJECT_ID('VaiTroNguoiDung') is not null
drop table VaiTroNguoiDung
go
create table VaiTroNguoiDung
(
	MaVaiTro bigint not null,
	MaNguoiDung bigint not null,
	VaiTro int not null,
	MoTaVaiTro nvarchar(255),
	MaTruong bigint not null,
	MaChuyenNganh bigint not null,
	Email nvarchar(255) not null,
	constraint pk_VaiTroNguoiDung primary key (MaVaiTro),
	constraint fk_VaiTroNguoiDung_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung),
	constraint fk_VaiTroNguoiDung_Truong foreign key (MaTruong) references Truong(MaTruong),
	constraint fk_VaiTroNguoiDung_ChuyenNganh foreign key (MaChuyenNganh) references ChuyenNganh(MaChuyenNganh)
)

go

--table KhoaHoc
if OBJECT_ID('KhoaHoc') is not null
drop table KhoaHoc
go
create table KhoaHoc
(
	MaKhoaHoc bigint identity(1, 1) not null,
	TenKhoaHoc nvarchar(255) not null,
	MaChuyenNganh bigint not null,
	MaTruong bigint not null,
	MaNguoiQuanLy bigint not null,
	NgayBatDau date not null,
	MoTaKhoaHoc nvarchar(1000),
	constraint pk_KhoaHoc primary key (MaKhoaHoc),
	constraint fk_KhoaHoc_Truong foreign key (MaTruong) references Truong(MaTruong),
	constraint fk_KhoaHoc_ChuyenNganh foreign key (MaChuyenNganh) references ChuyenNganh(MaChuyenNganh),
	constraint fk_KhoaHoc_NguoiDung foreign key (MaNguoiQuanLy) references NguoiDung(MaNguoiDung)
)

go

--table BaiTapKhoaHoc
if OBJECT_ID('BaiTapKhoaHoc') is not null
drop table BaiTapKhoaHoc
go
create table BaiTapKhoaHoc
(
	MaBaiTapKhoaHoc bigint identity(1,1) not null,
	TenBaiTapKhoaHoc nvarchar(255) not null,
	MaKhoaHoc bigint not null,
	MoTaBaiTapKhoaHoc nvarchar(1000),
	NgayBatDau date not null,
	NgayKetThuc date not null,
	GioKetThuc time not null,
	DuongDan nvarchar not null,
	constraint pk_BaiTapKhoaHoc primary key (MaBaiTapKhoaHoc),
	constraint fk_BaiTapKhoaHoc_KhoaHoc foreign key (MaKhoaHoc) references KhoaHoc(MaKhoaHoc)
)

go

--table BaiTapNop
if OBJECT_ID('BaiTapNop') is not null
drop table BaiTapNop
go
create table BaiTapNop
(
	MaBaiTapKhoaHoc bigint not null,
	MaNguoiDung bigint not null,
	constraint pk_BaiTapNop primary key (MaBaiTapKhoaHoc),
	constraint fk_BaiTapNop_BaiTapKhoaHoc foreign key (MaBaiTapKhoaHoc) references BaiTapKhoaHoc(MaBaiTapKhoaHoc),
	constraint fk_BaiTapNop_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung)
)

go

--table ThanhVienKhoaHoc
if OBJECT_ID('ThanhVienKhoaHoc') is not null
drop table ThanhVienKhoaHoc
go
create table ThanhVienKhoaHoc
(
	MaKhoaHoc bigint not null,
	MaNguoiDung bigint not null,
	LoaiNguoiDung int not null,
	constraint pk_ThanhVienKhoaHoc primary key (MaKhoaHoc, MaNguoiDung),
	constraint fk_ThanhVienKhoaHoc_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung),
	constraint fk_ThanhVienKhoaHoc_KhoaHoc foreign key (MaKhoaHoc) references KhoaHoc(MaKhoaHoc)
)

go

--table Nhom
if OBJECT_ID('Nhom') is not null
drop table Nhom
go
create table Nhom
(
	MaNhom bigint identity(1, 1) not null,
	TenNhom nvarchar(255) not null,
	MoTaNhom nvarchar(1000),
	MaNguoiQuanLy bigint not null,
	constraint pk_Nhom primary key (MaNhom),
	constraint fk_Nhom_NguoiDung foreign key (MaNguoiQuanLy) references NguoiDung(MaNguoiDung)
)

go

--table ThanhVienNhom
if OBJECT_ID('ThanhVienNhom') is not null
drop table ThanhVienNhom
go
create table ThanhVienHom
(
	MaNhom bigint not null,
	MaNguoiDung bigint not null,
	constraint pk_ThanhVienNhom primary key (MaNhom, MaNguoiDung),
	constraint fk_ThanhVienNhom_Nhom foreign key (MaNhom) references Nhom(MaNhom),
	constraint fk_ThanhVienNhom_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung)
)

go

--table QuanHe
if OBJECT_ID('QuanHe') is not null
drop table QuanHe
go
create table QuanHe
(
	MaNguoiDung1 bigint not null,
	MaNguoiDung2 bigint not null,
	constraint pk_QuanHe primary key (MaNguoiDung1, MaNguoiDung2),
	constraint fk_QuanHe_NguoiDung_QuanHe1 foreign key (MaNguoiDung1) references NguoiDung(MaNguoiDung),
	constraint fk_QuanHe_NguoiDung_QuanHe2 foreign key (MaNguoiDung2) references NguoiDung(MaNguoiDung)
)

go

--table ChuDe
if OBJECT_ID('ChuDe') is not null
drop table ChuDe
go
create table ChuDe
(
	MaChuDe bigint identity(1,1) not null,
	MaNguoiDung bigint not null,
	MaTrangChuNguoiDung bigint not null,
	Loai int not null,
	NoiDung nvarchar(1000) not null,
	DuongDan nvarchar(1000) not null,
	SoLuongThich int not null,
	SoLuongComment int not null,
	NgayGio datetime not null,
	constraint pk_ChuDe primary key (MaChuDe),
	constraint fk_ChuDe_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung),
	constraint fk_ChuDe_NguoiDung_TrangChu foreign key (MaTrangChuNguoiDung) references NguoiDung(MaNguoiDung)
)

go

--table TraLoi
if OBJECT_ID('TraLoi') is not null
drop table TraLoi
go
create table TraLoi
(
	MaTraLoi bigint identity(1,1) not null,
	MaNguoiDung bigint not null,
	MaChuDe bigint not null,
	DuongDan nvarchar(1000) not null,
	SoLuongThich int not null,
	NoiDung nvarchar(1000) not null,
	NgayGio datetime not null,
	constraint pk_TraLoi primary key (MaTraLoi),
	constraint fk_TraLoi_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung),
	constraint fk_TraLoi_ChuDe foreign key (MaChuDe) references ChuDe(MaChuDe)
)

go

--table Thich
if OBJECT_ID('Thich') is not null
drop table Thich
go
create table Thich
(
	MaNguoiDung bigint not null,
	MaNoiDungThich bigint not null,
	LoaiNoiDung int not null,
	constraint pk_Thich primary key (MaNguoiDung, MaNoiDungThich, LoaiNoiDung),
	constraint fk_MaNoiDungThich_ChuDe foreign key (MaNoiDungThich) references ChuDe(MaChuDe),
	constraint fk_MaNoiDungThich_TraLoi foreign key (MaNoiDungThich) references TraLoi(MaTraLoi)
)
	
go 

--table Hinh
if OBJECT_ID('Hinh') is not null
drop table Hinh
go
create table Hinh
(
	MaHinh bigint identity(1, 1) not null,
	MaNguoiDung bigint not null,
	TenHinh nvarchar(255) not null,
	DuongDanNguon nvarchar(255) not null,
	KichThuoc int not null,
	Ngay Date not null,
	MoTaHinh nvarchar(255),
	constraint pk_Hinh primary key (MaHinh),
	constraint fk_Hinh_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung)
)

go

--table Phim
if OBJECT_ID('Phim') is not null
drop table Phim
go
create table Phim
(
	MaPhim bigint identity(1,1) not null,
	MaNguoiDung bigint not null,
	TenPhim nvarchar(255) not null,
	DuongDanNguon nvarchar(255) not null,
	KichThuoc int not null,
	Ngay Date not null,
	MoTaPhim nvarchar(255),
	constraint pk_Phim primary key (MaPhim),
	constraint fk_Phim_NguoiDung foreign key (MaNguoiDung) references NguoiDung(MaNguoiDung)
)

go

alter table NguoiDung
add MaHinhDaiDien bigint not null
go
alter table NguoiDung
add constraint fk_NguoiDung_Hinh foreign key (MaHinhDaiDien) references Hinh(MaHinh)

